const crypto = require('crypto');
const { getMainKnex } = require('../database/knex');

/**
 * DataSchema - Handles table definitions and schema management
 */
class DataSchema {
    /**
     * Get table name for data type
     */
    static getTableName(dataType) {
        const tableMap = {
            'personal_info': 'personal_info',
            'bank': 'bank_transactions',
            'call': 'call_records',
            'travel': 'travel_records',
            'accommodation': 'accommodation_records',
            'property': 'property_records',
            'railway': 'railway_tickets',
            'flight': 'flight_tickets',
            'business': 'business_entities'
        };
        return tableMap[dataType] || dataType;
    }

    /**
     * 从 sys_schemas 获取表字段定义
     */
    static async getSchemaFields(dataType) {
        try {
            const mainKnex = getMainKnex();
            const tableName = this.getTableName(dataType);

            // 尝试查找 case_ 前缀的 schema (案件数据表)
            let schema = await mainKnex('sys_schemas')
                .where('name', `case_${tableName}`)
                .first();

            // 如果没找到，尝试不带前缀的
            if (!schema) {
                schema = await mainKnex('sys_schemas')
                    .where('name', tableName)
                    .first();
            }

            if (schema && schema.fields) {
                const fields = typeof schema.fields === 'string'
                    ? JSON.parse(schema.fields)
                    : schema.fields;
                return fields;
            }
            return null;
        } catch (error) {
            console.error(`[DataSchema] Error loading schema: ${error.message}`);
            return null;
        }
    }

    /**
     * Ensure table exists with proper schema
     */
    static async ensureTableExists(knex, tableName, dataType) {
        const exists = await knex.schema.hasTable(tableName);

        if (!exists) {
            // 尝试从 Schema 读取字段
            const schemaFields = await this.getSchemaFields(dataType);

            await knex.schema.createTable(tableName, (table) => {
                table.increments('id').primary();
                table.integer('case_id');

                if (schemaFields && schemaFields.length > 0) {
                    // 使用 Schema 定义的字段
                    console.log(`[DataSchema] Creating table ${tableName} from schema with ${schemaFields.length} fields`);
                    for (const field of schemaFields) {
                        // Skip internal fields as they are already added explicitly below
                        if (['id', 'case_id', 'row_hash', 'extra_data', 'created_at', 'updated_at'].includes(field.name)) continue;
                        this._addColumnFromField(table, field);
                    }
                } else {
                    // 回退到硬编码
                    console.log(`[DataSchema] Creating table ${tableName} using hardcoded schema`);
                    this._createHardcodedTable(table, dataType);
                }

                table.string('row_hash').unique();
                table.text('extra_data');
                table.timestamps(true, true);
            });
            console.log(`[DataSchema] Created table ${tableName}`);
        }

        // For existing tables, check and add extra_data column if missing
        const tableInfo = await knex.raw(`PRAGMA table_info(${tableName})`);
        const existingColumns = tableInfo.map(col => col.name);

        if (!existingColumns.includes('extra_data')) {
            await knex.raw(`ALTER TABLE ${tableName} ADD COLUMN extra_data TEXT`);
        }

        // Add missing columns based on data type to ensure compatibility with frontend mappings
        const requiredColumns = this.getRequiredColumnsForType(dataType);
        for (const [colName, colDef] of Object.entries(requiredColumns)) {
            if (!existingColumns.includes(colName)) {
                let typeStr = 'TEXT';
                if (colDef.type === 'decimal' || colDef.type === 'real') typeStr = 'REAL';
                else if (colDef.type === 'integer') typeStr = 'INTEGER';
                else if (colDef.type === 'datetime') typeStr = 'DATETIME';

                // SQLite simple alter
                await knex.raw(`ALTER TABLE ${tableName} ADD COLUMN ${colName} ${typeStr}`);
                console.log(`[DataSchema] Added missing column ${colName} to ${tableName}`);
            }
        }
    }

    static _createHardcodedTable(table, dataType) {
        switch (dataType) {
            case 'bank':
                table.datetime('transaction_time');
                table.string('account_number');
                table.string('counterparty_account');
                table.decimal('transaction_amount', 15, 2);
                table.decimal('amount', 15, 2);
                table.string('transaction_type');
                table.decimal('balance_after', 15, 2);
                table.decimal('balance', 15, 2);
                table.text('remarks');
                table.text('summary');
                table.string('customer_name');
                table.string('bank_name');
                table.string('counterparty_name');
                table.string('opponent_name');
                table.string('opponent_account');
                table.string('debit_credit_flag');
                table.string('transaction_branch_name');
                table.string('account_name');
                table.string('card_number');
                break;
            case 'call':
                table.string('user_number');
                table.string('phone_number');
                table.string('opp_user_number');
                table.string('other_number');
                table.datetime('start_time');
                table.datetime('call_time');
                table.integer('duration');
                table.string('call_type');
                table.string('user_name');
                table.string('lac_id');
                table.string('cell_id');
                table.string('location');
                break;
        }
    }

    static _addColumnFromField(table, field) {
        const { name, type, required } = field;
        let col;
        switch ((type || 'string').toLowerCase()) {
            case 'string': case 'text': col = table.string(name); break;
            case 'integer': case 'int': col = table.integer(name); break;
            case 'float': case 'real': case 'number': case 'decimal': col = table.decimal(name, 15, 2); break;
            case 'datetime': case 'date': col = table.datetime(name); break;
            case 'boolean': col = table.boolean(name); break;
            case 'json': col = table.json(name); break;
            default: col = table.string(name);
        }
        if (required) col.notNullable();
    }

    static getRequiredColumnsForType(dataType) {
        const columns = {};
        switch (dataType) {
            case 'bank':
                columns.transaction_time = { type: 'datetime' };
                columns.account_number = { type: 'string' };
                columns.counterparty_account = { type: 'string' };
                columns.transaction_amount = { type: 'decimal' };
                columns.amount = { type: 'decimal' };
                columns.transaction_type = { type: 'string' };
                columns.balance_after = { type: 'decimal' };
                columns.balance = { type: 'decimal' };
                columns.remarks = { type: 'string' };
                columns.summary = { type: 'string' };
                columns.customer_name = { type: 'string' };
                columns.bank_name = { type: 'string' };
                columns.counterparty_name = { type: 'string' };
                columns.opponent_name = { type: 'string' };
                columns.opponent_account = { type: 'string' };
                columns.debit_credit_flag = { type: 'string' };
                columns.transaction_branch_name = { type: 'string' };
                columns.account_name = { type: 'string' };
                columns.card_number = { type: 'string' };
                columns.row_hash = { type: 'string' };
                break;
            case 'call':
                columns.user_number = { type: 'string' };
                columns.phone_number = { type: 'string' };
                columns.opp_user_number = { type: 'string' };
                columns.other_number = { type: 'string' };
                columns.start_time = { type: 'datetime' };
                columns.call_time = { type: 'datetime' };
                columns.duration = { type: 'integer' };
                columns.call_type = { type: 'string' };
                columns.user_name = { type: 'string' };
                columns.lac_id = { type: 'string' };
                columns.cell_id = { type: 'string' };
                columns.location = { type: 'string' };
                columns.row_hash = { type: 'string' };
                break;
            case 'personal_info':
                ['name', 'id_card', 'gender', 'birth_date', 'ethnic', 'address', 'phone', 'workplace', 'position',
                    'education', 'political_status', 'marital_status', 'occupation', 'province', 'native_place',
                    'other_address', 'blood_type', 'former_name', 'alias', 'height', 'appearance', 'military_status',
                    'zodiac', 'chinese_zodiac', 'behavior_tags', 'row_hash'].forEach(f => columns[f] = { type: 'string' });
                break;
        }
        return columns;
    }

    /**
     * Generate row hash for deduplication
     */
    static generateRowHash(row, dataType) {
        const normalizeDate = (dateValue) => {
            if (!dateValue) return '';
            try {
                const date = new Date(dateValue);
                if (isNaN(date.getTime())) return dateValue;
                const pad = (n) => n.toString().padStart(2, '0');
                return `${date.getFullYear()}/${pad(date.getMonth() + 1)}/${pad(date.getDate())} ${pad(date.getHours())}:${pad(date.getMinutes())}:${pad(date.getSeconds())}`;
            } catch (e) { return dateValue; }
        };

        let rawString = '';
        switch (dataType) {
            case 'bank':
                rawString = `${normalizeDate(row.transaction_time)}|${row.transaction_amount || 0}|${row.balance_after || 0}|${row.counterparty_account || ''}|${row.remarks || ''}`;
                break;
            case 'call':
                rawString = `${row.user_number || ''}|${row.opp_user_number || ''}|${normalizeDate(row.start_time)}|${row.duration || 0}`;
                break;
            case 'travel':
                rawString = `${normalizeDate(row.start_time)}|${row.origin || ''}|${row.destination || ''}`;
                break;
            case 'accommodation':
                rawString = `${normalizeDate(row.check_in_time)}|${row.hotel_name || ''}|${row.guest_name || ''}`;
                break;
            case 'property':
                rawString = `${row.property_address || ''}|${row.owner || ''}`;
                break;
            case 'personal_info':
                rawString = `${row.id_card || ''}|${row.name || ''}`;
                break;
            default:
                return null;
        }

        return crypto.createHash('md5').update(rawString).digest('hex');
    }
}

module.exports = DataSchema;
